/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dataentry;

import au.com.bytecode.opencsv.CSVWriter;
import com.mysql.jdbc.ResultSetMetaData;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.Id;


/**
 *
 * @author i.capurro
 */

@Entity
public class Persistence implements Serializable {
    @Id
    private String formpath = "forms";
    private String url = "localhost";
    private String username = "root";
    private String password = "";
    private String DBname = "data_entry";
    static private Persistence p = null;

    public Persistence() {
    }

    static public Persistence getInstance() {
        if (p == null) {
            p = new Persistence();
        }
        return p;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setUser(String user) {
        this.username = user;
    }
    
    public void setPath(String path) {
        this.formpath = path;
    }

    public void setDBname(String DBname) {
        this.DBname = DBname;
    }

    public String getDBname() {
        return DBname;
    }

    public String getPassword() {
        return password;
    }

    public String getPath() {
        return formpath;
    }

    public String getUrl() {
        return url;
    }

    public String getUser() {
        return username;
    }
    
private String[] concat(String[] A, String[] B) {
    if (A == null) {
        return B;
    }
    if (B == null) {
        return A;
    }
    String[] C= new String[A.length+B.length];
    System.arraycopy(A, 0, C, 0, A.length);
    System.arraycopy(B, 0, C, A.length, B.length);

    return C;
}
    
    public void save(ArrayList list) throws IOException {
        Iterator it = list.iterator();
        List<String[]> escribir = new ArrayList<>();
        String[] form = null;
        while (it.hasNext()) {
            Question aux = (Question) it.next();
            String[] str = new String[aux.getLength()];
            Iterator answers = aux.getAnswers().iterator();
            int i = 0;
            while (answers.hasNext()) {
                String val = (String) answers.next();
                if (val != null) {
                    str[i] = val;
                } else {
                    str[i] = "";
                }
                i++;
            }
            form = concat(form, str);
        }

        escribir.add(form);
        
        File save = new File(formpath);
        CSVWriter writer = new CSVWriter(new FileWriter(save), ';');
        writer.writeAll(escribir);
        writer.flush();
    }
    
    public void saveToTable(String name, ArrayList list) throws IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Iterator it = list.iterator();
        List<String[]> escribir = new ArrayList<>();
        Question aux = (Question) it.next();
        String values = aux.getAnswer();
        while (it.hasNext()) {
            aux = (Question) it.next();
            Iterator answers = aux.getAnswers().iterator();
            for (int i = 0; i < aux.getLength(); i++) {
                if (answers.hasNext()) {
                    String val = (String) answers.next();
                    values = values + "," + val;
                } else {
                    values = values + ",NULL";
                }
            }
        }

        Class.forName("com.mysql.jdbc.Driver").newInstance();

        String sql = "INSERT INTO " + name + " VALUES (" + values + ")";

        Connection c = DriverManager.getConnection("jdbc:mysql://" + url + "/" + DBname, username, password);
        Statement stmt = c.createStatement();
        int rs = stmt.executeUpdate(sql);
        c.close();
        
    }
    
    public void createTable(String name, List columns) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            
            String sql = "CREATE TABLE IF NOT EXISTS "+ name +" (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY";
            
            Iterator it = columns.iterator();
            
            while (it.hasNext()) {
                sql = sql + ", " + ((String) it.next()) + " INT";
            }
            
            sql = sql + ")";
            
            Connection c = DriverManager.getConnection("jdbc:mysql://" + url + "/" + DBname, username, password);
            Statement stmt = c.createStatement();
            int rs = stmt.executeUpdate(sql);
            c.close();
        }
    
    public boolean validateID(String ID, String name) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        
        String sql = "SELECT id FROM " + name + " WHERE id = " + ID + ";";
        
        Connection c = DriverManager.getConnection("jdbc:mysql://" + url + "/" + DBname, username, password);
        Statement stmt = c.createStatement();
        java.sql.ResultSet rs = stmt.executeQuery(sql);
        boolean response = !rs.first();
        c.close();
        return response;
    }
    
    public List getTableHeader(String tname) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String sql = "SHOW COLUMNS FROM " + tname + ";";
        Connection c = DriverManager.getConnection("jdbc:mysql://" + url + "/" + DBname, username, password);
        Statement stmt = c.createStatement();
        java.sql.ResultSet rs = stmt.executeQuery(sql);
        
        List header = new ArrayList();
        
        while (rs.next()) {
            header.add(rs.getString("Field"));
        }
        c.close();
        return header;
    }
    
    public List getTableData(String tname) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String sql = "SELECT * FROM " + tname + ";";
        Connection c = DriverManager.getConnection("jdbc:mysql://" + url + "/" + DBname, username, password);
        Statement stmt = c.createStatement();
        java.sql.ResultSet rs = stmt.executeQuery(sql);
        
        List data = new ArrayList();
        
        while (rs.next()) {
            ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
            ArrayList row = new ArrayList();
            for (int i = 0; i < md.getColumnCount(); i++) {
                row.add(rs.getObject(i+1));
            }
            data.add(row);
        }
        c.close();
        return data;
    }
    
    private Object readResolve() throws ObjectStreamException {
        return p;
  }
    private void readObject(ObjectInputStream ois) throws IOException, ClassNotFoundException {
        ois.defaultReadObject();
        synchronized (Persistence.class) {
            if (p == null) {
                p = this;
            }
        }
    }
    
    public Long getQuantity (String name) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String sql = "SELECT count(*) FROM " + name + ";";
        Connection c = DriverManager.getConnection("jdbc:mysql://" + url + "/" + DBname, username, password);
        Statement stmt = c.createStatement();
        java.sql.ResultSet rs = stmt.executeQuery(sql);
        
        rs.next();
        Long q = (Long) rs.getObject(1);
        
        return q;
    }
}
